USE CX_Update_History
GO
--======================================================================================
--CREATION KEYS
--========================================================================================
--create one Database Master Key
Create master key
Encryption by Password = 'cxVirtualDmKey'

--Create One Certificate
Create Certificate CXUserCert
With Subject= 'Certificate to encrypt tblUser table',
Start_date = '2010-08-26',
Expiry_date ='2099-12-31'
GO

--Create Symmetric Key
Create Symmetric Key CXUserSym
With KEY_SOURCE = 'This is key source for CXUserSym',
IDENTITY_VALUE = 'This is identity value for CXUserSym',
Algorithm = AES_256
Encryption by Certificate CXUserCert


Insert Into tblUser(UserName, UserRole, Location, AuthCode, CreatedDate, ModifiedDate)
Values('roy', 'Marketing Manager', 'Malaysia', ENCRYPTBYKEY(Key_guid(N'CXUserSym'), 'roy123'), GETDATE(), GETDATE())

Open Symmetric Key CXUserSym Decryption By Certificate CXUserCert;

Update tblUser Set AuthCode = ENCRYPTBYKEY(Key_Guid(N'CXUserSym'), 'Pass1234')
Where UserId = 1

Select UserName, UserRole, Location, CONVERT(varchar(50),DECRYPTBYKEY(AuthCode)) As AuthCode, CreatedDate, ModifiedDate from tblUser 
Where UserName = 'Schahryar'
Close Symmetric Key CXUserSym

EXEC sprocUserSelectItemByUserName 'phyo'

ALTER Procedure sprocUserSelectItemByUserName
	@UserName varchar(50)
	As
	
	Open Symmetric Key CXUserSym Decryption By Certificate CXUserCert;
	Select UserId, UserName, UserRole, Location, CONVERT(varchar(50),DECRYPTBYKEY(AuthCode)) As AuthCode, CreatedDate, ModifiedDate from tblUser 
	Where UserName = @UserName
	Close Symmetric Key CXUserSym
						
ALTER Procedure sprocUpdateHistoryInsertUpdateSingleItem
	@HistoryId bigint,
	@CXUserId int,
	@Title varchar(50),
	@Description varchar(500),
	@TestStatus int,
	@LiveStatus int,
	@Remark varchar(200)
As
	Declare @ReturnValue bigint
	
	IF @HistoryId = -1
		BEGIN
			INSERT INTO tblUpdateHistory
					(CXUserId,
					Title,
					Description,
					TestStatus,
					LiveStatus,
					Remark,
					CreatedDate,
					ModifiedDate)
					Values
					(@CXUserId,
					@Title,
					@Description,
					@TestStatus,
					@LiveStatus,
					@Remark,
					GETDATE(),
					GETDATE())
					
					Set @ReturnValue = SCOPE_IDENTITY()
					
					INSERT INTO tblUpdateHistoryLog
						(HistoryId,
						CXUserId,
						Title,
						Description,
						TestStatus,
						LiveStatus,
						Remark,
						UpdateStatus,
						CreatedDate,
						ModifiedDate)
						Values
						(@ReturnValue,
						@CXUserId,
						@Title,
						@Description,
						@TestStatus,
						@LiveStatus,
						@Remark,
						0,
						GETDATE(),
						GETDATE())
		END
	ELSE
		BEGIN
			UPDATE tblUpdateHistory SET
					CXUserId = @CXUserId,
					Title = @Title,
					Description = @Description,
					TestStatus = @TestStatus,
					LiveStatus = @LiveStatus,
					Remark = @Remark,
					ModifiedDate = GETDATE()
			WHERE HistoryId = @HistoryId
			
			SET @ReturnValue = @HistoryId
			
			INSERT INTO tblUpdateHistoryLog
						(HistoryId,
						CXUserId,
						Title,
						Description,
						TestStatus,
						LiveStatus,
						Remark,
						UpdateStatus,
						CreatedDate,
						ModifiedDate)
						Values
						(@HistoryId,
						@CXUserId,
						@Title,
						@Description,
						@TestStatus,
						@LiveStatus,
						@Remark,
						1,
						GETDATE(),
						GETDATE())
		END
		
	IF (@@ERROR != 0)
	BEGIN
		RETURN -1
	END
	ELSE
	BEGIN
		RETURN @ReturnValue
	END
	
Create Procedure sprocUpdateHistorySelectSingleItem
@HistoryId bigint
AS
Set NoCount On
Select * from tblUpdateHistory Where HistoryId = @HistoryId 

Set NoCount Off

EXEC sprocUpdateHistorySelectItemList 0, 0, -1

SELECT h.HistoryId, h.CXUserId, u.UserName, h.Title, h.Description,        
CASE h.TestStatus 
WHEN 0 THEN '<span style=color:blue;>Not Publish</span>' 
WHEN 1 THEN '<span style=color:red;>Publish</span>' 
WHEN 2 THEN '<span style=color:green;>OK</span>' 
END AS TestStatus,        

CASE h.LiveStatus 
WHEN 0 THEN '<span style=color:blue;>Not Publish</span>' 
WHEN 1 THEN '<span style=color:red;>Publish</span>' 
WHEN 2 THEN '<span style=color:green;>OK</span>' 
END AS LiveStatus, h.Remark, h.CreatedDate, h.M Where 1 = 1 And h.TestStatus = 0 And h.LiveStatus = 0

ALTER Procedure sprocUpdateHistorySelectItemList
@TestStatus int,
@LiveStatus int,
@CXUserId int
As
	Set NoCount On
	Declare @strSQL varchar(1000)
	Declare @strWhere varchar(100)
	
	Set @strSQL = 'SELECT h.HistoryId, h.CXUserId, u.UserName, h.Title, h.Description, 
					CASE h.TestStatus WHEN 0 THEN ''<span style=color:blue;>Not Publish</span>'' WHEN 1 THEN ''<span style=color:red;>Publish</span>'' WHEN 2 THEN ''<span style=color:green;>OK</span>'' END AS TestStatus, 
					CASE h.LiveStatus WHEN 0 THEN ''<span style=color:blue;>Not Publish</span>'' WHEN 1 THEN ''<span style=color:red;>Publish</span>'' WHEN 2 THEN ''<span style=color:green;>OK</span>'' END AS LiveStatus, h.Remark, h.CreatedDate, h.ModifiedDate
					FROM tblUpdateHistory AS h INNER JOIN tblUser AS u ON h.CXUserId = u.UserId'
	Set @strWhere = ' Where 1 = 1'
	
	IF(@CXUserId > -1)
	BEGIN
		Set @strWhere = @strWhere + ' And h.CXUserId = ' + CAST(@CXUserId as varchar)
	END
	
	IF(@TestStatus > -1)
	BEGIN
		Set @strWhere = @strWhere + ' And h.TestStatus = ' + CAST(@TestStatus as varchar)
	END
	
	IF(@LiveStatus > -1)
	BEGIN
		Set @strWhere = @strWhere + ' And h.LiveStatus = ' + CAST(@LiveStatus as varchar)
	END
	
	--Select (@strSQL + @strWhere)
	EXEC (@strSQL + @strWhere)
	
ALTER procedure sprocUserUpdateHistoryLogSelectItemListByHistoryId
@HistoryId bigint
As

SELECT    h.HistoryLogId, h.HistoryId, h.CXUserId, u.UserName, h.Title, h.Description, CASE h.TestStatus WHEN 0 THEN 'Not Publish' WHEN 1 THEN 'Publish' WHEN 2 THEN 'Testing OK' END AS TestStatus, 
					CASE h.LiveStatus WHEN 0 THEN 'Not Publish' WHEN 1 THEN 'Publish' WHEN 2 THEN 'Testing OK' END AS LiveStatus, h.Remark, 
					CASE h.UpdateStatus WHEN 0 THEN 'New' WHEN 1 THEN 'Update' END UpdateStatus, h.CreatedDate, h.ModifiedDate
					FROM         tblUpdateHistoryLog AS h INNER JOIN tblUser AS u ON h.CXUserId = u.UserId WHERE HistoryId = @HistoryId	
	
	
